package com.website.travle.mapper;

import com.website.travle.pojo.User;
import com.website.travle.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class IUserDAOImpl implements IUserDAO {


    @Override
    public int login(String username, String password) throws SQLException {
        ResultSet resultSet = JdbcUtils.executeQuery("select * from user where username = ? and password = ?", username, password);
        if (resultSet.next()) {
            return 1;
        }
        return 0;
    }

    @Override
    public int add(User user) throws SQLException {
        // 添加前先判断用户名是否存在
        ResultSet resultSet = JdbcUtils.executeQuery("select * from user where username = ?", user.getUsername());
        if (resultSet.next()) {
            return 0;
        }
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into user values (null,?,?,?,?,?)");
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getPassword());
        preparedStatement.setString(3, user.getNickname());
        preparedStatement.setString(4, user.getPhone());
        preparedStatement.setInt(5, user.getIdentity());
        preparedStatement.execute();
        return 1;
    }

    @Override
    public int update(User user) throws SQLException {
        JdbcUtils.executeUpdate("update user set username = ?,password = ?,nickname = ?,phone = ?,identity = ? where id = ?", user.getUsername(), user.getPassword(), user.getNickname(), user.getPhone(), user.getIdentity(), user.getId());
        return 1;
    }

    @Override
    public int delete(int id) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from user where id = ?");
        preparedStatement.setInt(1, id);
        preparedStatement.execute();
        return 1;
    }

    @Override
    public List<User> pageByLikeName(Integer page, Integer limit, String name) throws SQLException {
        int currentPage = page; // 当前页码
        int pageSize = limit; // 每页显示的记录数
        int offset = (currentPage - 1) * pageSize; // 查询结果的偏移量

        ResultSet resultSet = null;
        if (name == null) {
            resultSet = JdbcUtils.executeQuery("select * from user limit ?,?", offset, pageSize);
        } else {
            name = "%" + name + "%";
            resultSet = JdbcUtils.executeQuery("select * from user where username like ? limit ?,?", name, offset, pageSize);
        }
        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User(resultSet.getInt("id"), resultSet.getString("username"), resultSet.getString("password"), resultSet.getString("nickname"), resultSet.getString("phone"), resultSet.getInt("identity"));
            userList.add(user);
        }
        return userList;
    }

    @Override
    public int getTotal() throws SQLException {
        ResultSet resultSet = JdbcUtils.executeQuery("select count(*) from user");
        while (resultSet.next()) {
            return resultSet.getInt(1);
        }
        return 0;
    }

    public static void main(String[] args) throws Exception {
        IUserDAOImpl userDAO = new IUserDAOImpl();
//        System.out.println(userDAO.login("admin", "123456"));
//        userDAO.add(new User(null, "admin6", "123456", "测试有", "12345678901", 1));
//        userDAO.update(new User(1, "admin", "123456", "钉钉", "12345678901", 1));
//        System.out.println(userDAO.delete(3));
        List<User> userList = userDAO.pageByLikeName(1, 2, "a");
        for (User user : userList) {
            System.out.println(user);
        }
//        System.out.println(userDAO.getTotal());
    }

}
